use crate::{Exception, Object, SqlCrud};
use crate::sqlite_util::FromSqliteRow;
use sqlite::{Connection, State};
use std::collections::HashMap;


/// SQLite database client with CRUD operation encapsulation; during queries,
/// it can perform struct mapping based on structs or return HashMap objects
///
/// ## 示例
/// ```rust
/// fn it_works() -> Result<(), Exception> {
///     let client = SqliteClient::new("./docs/test.db")?;
///     let result = client.select("SELECT id, name FROM users")?;
///     println!("{:?}", result);
///     let x = result[0].get("name").unwrap().get_string().unwrap();
///     println!("{:?}", x);
///     let x = result[0].get("id").unwrap().get_string().unwrap();
///     println!("{:?}", x);
///     Ok(())
/// }
/// ```
pub struct SqliteClient {
    pub conn: Connection,
}

impl SqliteClient {
    pub fn new(db_path: &str) -> Result<Self, Exception> {
        let conn = Connection::open(db_path)?;
        Ok(Self { conn })
    }

    /// 执行SQL语句
    ///
    /// 该函数用于执行不返回结果集的SQL语句（如INSERT、UPDATE、DELETE等）。
    /// 如果SQL语句返回结果集，将返回错误。
    ///
    /// # 参数
    /// * `sql` - 要执行的SQL语句字符串
    ///
    /// # 返回值
    /// * `Ok(())` - SQL语句执行成功
    /// * `Err(sqlite::Error)` - SQL语句执行失败或返回了结果集
    pub fn execute(&self, sql: &str) -> Result<(), sqlite::Error> {
        // 准备SQL语句
        let res = self.conn.execute(sql)?;
        Ok(res)
    }

    /// 执行SQL查询并返回结果集
    ///
    /// 该函数接收一个SQL查询语句，执行查询并将结果转换为指定类型T的向量。
    /// 类型T必须实现FromSqliteRow trait，以便能够从数据库行数据转换为该类型。
    ///
    /// # 参数
    /// * `sql` - 要执行的SQL查询语句字符串引用
    ///
    /// # 返回值
    /// 返回包含查询结果的Vec<T>，其中每个元素都是从查询结果行转换而来的T类型实例
    ///
    /// # 泛型约束
    /// T: FromSqliteRow - 要求类型T必须实现FromSqliteRow trait
    pub fn query<T: FromSqliteRow>(&self, sql: &str) -> Vec<T> {
        // 准备SQL语句
        let stmt = self.conn.prepare(sql).unwrap();
        let mut results = Vec::new();
        // 遍历查询结果，将每一行转换为指定类型并添加到结果集中
        for row in stmt.into_iter().map(|row| row.unwrap()) {
            results.push(T::from_row(&row));
        }
        results
    }

    /// 执行SQL查询并返回单个结果
    pub fn query_one<T: FromSqliteRow>(&self, sql: &str) -> Option<T> {
        // 准备SQL语句
        let stmt = self.conn.prepare(sql).unwrap();
        // 遍历查询结果，将每一行转换为指定类型并添加到结果集中
        for row in stmt.into_iter().map(|row| row.unwrap()) {
            return Some(T::from_row(&row));
        }
        None
    }

}

impl SqlCrud for SqliteClient {
    fn insert(&self, sql: &str) -> Result<bool, Exception> {
        match self.execute(sql) {
            Ok(_) => Ok(true),
            Err(_) => Ok(false),
        }
    }

    fn update(&self, sql: &str) -> Result<bool, Exception> {
        match self.execute(sql) {
            Ok(_) => Ok(true),
            Err(_) => Ok(false),
        }
    }

    fn delete(&self, sql: &str) -> Result<bool, Exception> {
        match self.execute(sql) {
            Ok(_) => Ok(true),
            Err(_) => Ok(false),
        }
    }

    fn select_one(&self, sql: &str) -> Result<HashMap<String, Object>, Exception> {
        // 准备SQL语句
        let stmt = self.conn.prepare(sql)?;
        let row = stmt.into_iter().next();
        if let Some(row) = row {
            let row = row.unwrap();
            let mut item: HashMap<String, Object> = HashMap::new();
            for (key, value) in row.iter() {
                item.insert(key.into(), value.into());
            }
            return Ok(item);
        }
        Err(Exception::DataNotFound)
    }

    fn select(&self, sql: &str) -> Result<Vec<HashMap<String, Object>>, Exception> {
        // 准备SQL语句
        let stmt = self.conn.prepare(sql)?;
        let mut results = Vec::new();
        // 遍历查询结果，将每一行转换为指定类型并添加到结果集中
        for row in stmt.into_iter().map(|row| row.unwrap()) {
            let mut item: HashMap<String, Object> = HashMap::new();
            for (key, value) in row.iter() {
                item.insert(key.into(), value.into());
            }
            results.push(item);
        }
        Ok(results)
    }
}

#[cfg(test)]
mod tests {
    use crate::db_sqlite::SqliteClient;
    use crate::{Exception, SqlCrud};

    #[test]
    fn it_works() -> Result<(), Exception> {
        let client = SqliteClient::new("./docs/test.db")?;
        let result = client.select("SELECT id, name FROM users")?;
        println!("{:?}", result);
        let x = result[0].get("name").unwrap().get_string().unwrap();
        println!("{:?}", x);
        let x = result[0].get("id").unwrap().get_string().unwrap();
        println!("{:?}", x);
        Ok(())
    }
}
